#!/usr/bin python3
# -*- encoding: utf-8 -*-
'''
@File    :   csvInsertMysql.py
@Time    :   2020/09/03 12:31:46
@Author  :   陈培杞
@Version :   1.0
'''

import pymysql
from pathlib import Path

#连接数据库
config = {'host':'192.168.200.172',
          'port':3309,
          'user':'root',
          'passwd':'123456',
          'local_infile':1
          }
conn = pymysql.connect(**config)
cur = conn.cursor()



def load_csv(csv_file_dir,table_name,database='test'):
    """ 
    1. windows 下 LINES TERMINATED BY '\\r\\n' 。 linux下 LINES TERMINATED BY '\\n'  
    2. csv含有表头 IGNORE 1 LINES
    3. 含有自增id时，要在最后加上括号()指定导入的列名，如果报错 ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
                                                则使用 mysql> ALTER TABLE `table_name`  AUTO_INCREMENT = 1  重建id，再执行导入
    """
    cur.execute('use %s' % database)
    for csvfile in Path(csv_file_dir).iterdir():
        if csvfile.suffix == '.csv':
            print("准备导入{}".format(csvfile))
            data_sql = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' (rcpt , sender, tag, region, domainrep,authuser,attachs, usertag)" % (csvfile,table_name)
            cur.execute(data_sql)
            conn.commit()
    conn.close()
    cur.close()
    
load_csv('/home/cacboy/userprofile', 'raw')